package tt.dz.biz.dao.financial;

import java.util.Date;
import java.util.List;
import java.util.Map;
import org.apache.commons.lang.StringUtils;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.stereotype.Repository;
import tt.dz.biz.dao.BaseDAO;
import tt.dz.vo.BorrowLoanCheckVO;
import tt.dz.vo.MerchAccountDetailVO;
import tt.dz.vo.MerchantSubjectAccVO;
import tt.dz.vo.TranInfoVO;

@Repository("merchantReportDAO")
public class MerchantReportDAO
  extends BaseDAO
{
  private static final String LIST_MERCHANT_DAILY_ACCOUNTING = "select  merchant_code,subject_code,sub_subject_code,loan_flag,amount,merchant_code1,accounting_date  from ms_merch_account_detail   where 1=1 ";
  private static final String LIST_BORROW_LOAN_CHECK = "select borrow_amount,loan_amount,is_bal,accounting_date from ms_borrow_loan_check  where borrow_amount !=0 and loan_amount !=0 ";
  private static final String CHANNEL_PAY_LIST = "SELECT \tti.channel_code,\tpc.channel_name,\tti.thd_pay_seq ,\tti.payment_amount,\tti.accounting_date, \tti.pay_state_code,\tti.receiver, mm.name as receiverName,\tub.name as payerName,\tub.mobile_no FROM ms_tran_info ti LEFT JOIN\tms_merchant mm on ti.receiver = mm.merchant_code,\tms_pay_channel pc,\tms_user_base ub  WHERE ti.original_payer = ub.user_id AND ti.channel_code = pc.channel_code  ";
  private static final String BALANCE_OF_PAYMENTS = "SELECT mad.trade_no,mti.card_no,mti.original_payer_name,osi.name as  serviceName,mti.plat_time,mti.payment_amount, mm.merchant_code as merchantCode ,mm.name as merchantName ,mti.accounting_date as accountingDate,  ms.subject_code as subjectCode,ms.subject_name as subjectName,mad.amount  FROM\tms_tran_info mti  LEFT JOIN order_service_info osi ON mti.service_no=osi.serviceno ,ms_merch_account_dividend mad,\tms_subject ms,\tms_merchant mm  WHERE mti.trade_no = mad.trade_no and mad.sub_subject_code = ms.subject_code   and  mad.merchant_code = mm.merchant_code   AND ms.parent_subject_code = '6001'  ";
  public static final String QUERY_MERCHANT_SUBJECT_ACC = "select merchant_code,sub_subject_code,acct_bal ,last_bal from ms_merchant_subject_acc   where  1=1 ";
  public static final String QUERY_MERCHANT_SUBJECT_ACC_FOR_FIRST_LEVEL = "select sa.merchant_code,ms.parent_subject_code as sub_subject_code,sum(sa.acct_bal) as  acctBal,sum(sa.last_bal) as  lastBal   from ms_merchant_subject_acc sa ,ms_subject ms where sa.sub_subject_code = ms.subject_code ";
  
  public List<MerchAccountDetailVO> queryMerchantDailyAccouting(MerchAccountDetailVO param)
  {
    StringBuilder sql = new StringBuilder("select  merchant_code,subject_code,sub_subject_code,loan_flag,amount,merchant_code1,accounting_date  from ms_merch_account_detail   where 1=1 ");
    if (null != param)
    {
      if (StringUtils.isNotBlank(param.getMerchantCode())) {
        sql.append(" and merchant_code =:merchantCode ");
      }
      if (StringUtils.isNotBlank(param.getMerchantCode1())) {
        sql.append(" and merchant_code1=:merchantCode1 ");
      }
      if (StringUtils.isNotBlank(param.getSubSubjectCode())) {
        sql.append(" and and sub_subject_code=:subSubjectCode ");
      }
      if (null != param.getStartDate()) {
        sql.append(" and accounting_date>=:startDate ");
      }
      if (null != param.getEndDate()) {
        sql.append(" and accounting_date<=:endDate ");
      }
    }
    return queryForList(sql.toString(), param, MerchAccountDetailVO.class);
  }
  
  public List<BorrowLoanCheckVO> queryBorrowLoanCheck(Date startDate, Date endDate)
  {
    StringBuilder sql = new StringBuilder("select borrow_amount,loan_amount,is_bal,accounting_date from ms_borrow_loan_check  where borrow_amount !=0 and loan_amount !=0 ");
    MapSqlParameterSource param = new MapSqlParameterSource();
    if (null != startDate)
    {
      sql.append(" and accounting_date>=:startDate ");
      param.addValue("startDate", startDate);
    }
    if (null != endDate)
    {
      sql.append(" and  accounting_date<=:endDate ");
      param.addValue("endDate", endDate);
    }
    return queryForList(sql.toString(), param, BorrowLoanCheckVO.class);
  }
  
  public List<TranInfoVO> queryChannelPayList(String channelCode, Date startDate, Date endDate)
  {
    StringBuilder sql = new StringBuilder("SELECT \tti.channel_code,\tpc.channel_name,\tti.thd_pay_seq ,\tti.payment_amount,\tti.accounting_date, \tti.pay_state_code,\tti.receiver, mm.name as receiverName,\tub.name as payerName,\tub.mobile_no FROM ms_tran_info ti LEFT JOIN\tms_merchant mm on ti.receiver = mm.merchant_code,\tms_pay_channel pc,\tms_user_base ub  WHERE ti.original_payer = ub.user_id AND ti.channel_code = pc.channel_code  ");
    MapSqlParameterSource param = new MapSqlParameterSource();
    if (StringUtils.isNotBlank(channelCode))
    {
      sql.append(" and ti.channel_code = :channelCode");
      param.addValue("channelCode", channelCode);
    }
    if (null != startDate)
    {
      sql.append("  and  ti.accounting_date >=:startDate  ");
      param.addValue("startDate", startDate);
    }
    if (null != endDate)
    {
      sql.append(" and  ti.accounting_date <=:endDate ");
      param.addValue("endDate", endDate);
    }
    sql.append(" order by channel_code asc,accounting_date  asc ");
    return queryForList(sql.toString(), param, TranInfoVO.class);
  }
  
  public List<Map<String, Object>> queryBalanceOfPayments(Date startDate, Date endDate, String merchantCode)
  {
    StringBuilder sql = new StringBuilder("SELECT mad.trade_no,mti.card_no,mti.original_payer_name,osi.name as  serviceName,mti.plat_time,mti.payment_amount, mm.merchant_code as merchantCode ,mm.name as merchantName ,mti.accounting_date as accountingDate,  ms.subject_code as subjectCode,ms.subject_name as subjectName,mad.amount  FROM\tms_tran_info mti  LEFT JOIN order_service_info osi ON mti.service_no=osi.serviceno ,ms_merch_account_dividend mad,\tms_subject ms,\tms_merchant mm  WHERE mti.trade_no = mad.trade_no and mad.sub_subject_code = ms.subject_code   and  mad.merchant_code = mm.merchant_code   AND ms.parent_subject_code = '6001'  ");
    MapSqlParameterSource param = new MapSqlParameterSource();
    if (StringUtils.isNotBlank(merchantCode))
    {
      sql.append(" and mad.merchant_code =:merchantCode");
      param.addValue("merchantCode", merchantCode);
    }
    if (null != startDate)
    {
      sql.append("  and mti.accounting_date >=:startDate  ");
      param.addValue("startDate", startDate);
    }
    if (null != endDate)
    {
      sql.append(" and  mti.accounting_date <=:endDate ");
      param.addValue("endDate", endDate);
    }
    sql.append(" order by mti.plat_time asc, mti.trade_no asc ,  mm.merchant_code asc ");
    
    List<Map<String, Object>> balanceList = queryForList(sql.toString(), param);
    return balanceList;
  }
  
  public List<MerchantSubjectAccVO> queryMerchantSubjectAcc(MerchantSubjectAccVO param)
  {
    StringBuilder sql = new StringBuilder("select merchant_code,sub_subject_code,acct_bal ,last_bal from ms_merchant_subject_acc   where  1=1 ");
    if (null != param)
    {
      if (StringUtils.isNotBlank(param.getMerchantCode())) {
        sql.append(" and merchant_code =:merchantCode ");
      }
      if (StringUtils.isNotBlank(param.getSubSubjectCode())) {
        sql.append(" and sub_subject_code=:subSubjectCode ");
      }
    }
    return queryForList(sql.toString(), param, MerchantSubjectAccVO.class);
  }
  
  public List<MerchantSubjectAccVO> queryMerchantAccForFirstLevelSubject(MerchantSubjectAccVO param)
  {
    StringBuilder sql = new StringBuilder("select sa.merchant_code,ms.parent_subject_code as sub_subject_code,sum(sa.acct_bal) as  acctBal,sum(sa.last_bal) as  lastBal   from ms_merchant_subject_acc sa ,ms_subject ms where sa.sub_subject_code = ms.subject_code ");
    if (null != param)
    {
      if (StringUtils.isNotBlank(param.getMerchantCode())) {
        sql.append(" and sa.merchant_code =:merchantCode ");
      }
      if (StringUtils.isNotBlank(param.getSubSubjectCode())) {
        sql.append("  and ms.parent_subject_code =:subSubjectCode ");
      }
    }
    sql.append(" group by sa.merchant_code,ms.parent_subject_code ");
    return queryForList(sql.toString(), param, MerchantSubjectAccVO.class);
  }
}
